package com.dhcc.android.base.db;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.dhcc.android.base.utils.StringUtil;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 外部db导入
 *
 * @author zhoudan
 * @date 2015年6月1日
 */
public class DBImportUtil {

    private static SQLiteDatabase db;

    /**
     * 打开外部db数据库
     *
     * @param root        任务包存储路径
     * @param taskPackage 任务包名 如："桐城市"
     * @param dbFilePath  db文件路径 如 ： "/TASK/DATA.db"
     */
    public static SQLiteDatabase openDatabase(String root, String taskPackage, String dbFilePath) {
//        String dbPath = CommenUtil.getSavePath() + "/" + taskPackage + "/Task/DATA.db";
        String dbPath = root + "/" + taskPackage + dbFilePath;

//        if(db == null){
        try {
            System.out.println("###数据库：" + dbPath);
            db = SQLiteDatabase.openOrCreateDatabase(dbPath, null);
            System.out.println("###获得db");
        } catch (Exception e) {
            e.printStackTrace();
        }
//        }

        return db;
    }


    /**
     * 获取数据列表
     *
     * @param table  表名
     * @param fields 搜索的字段名数组
     * @param key    搜索条件对应的字段名
     * @param value  搜索条件对应的值
     */
    public static List<Map<String, String>> listData(String table, String[] fields, String key, String value, String extendCondition) {
        List<Map<String, String>> list = new ArrayList<Map<String, String>>();
        String fstr = "";

        if (fields == null || fields.length == 0) {
            fstr = " * ";
            return null;
        } else {
            for (String str : fields) {
                fstr += " " + str + ",";
            }
            fstr = fstr.substring(0, fstr.length() - 1);
        }

        String wstr = "";
        String sql = "";
        if (StringUtil.isNotEmpty(key) && StringUtil.isNotEmpty(value)) {
            wstr = " WHERE " + key + " = '" + value + "'";
            sql = "SELECT " + fstr + " FROM " + table + " " + wstr + " AND " + extendCondition;
        } else {
            sql = "SELECT " + fstr + " FROM " + table + " " + " WHERE " + extendCondition;
        }

        System.out.println("###sql:" + sql);

        Cursor c = db.rawQuery(sql, null);

        while (c.moveToNext()) {
            Map<String, String> map = new HashMap<String, String>();
            for (String str : fields) {
                map.put(str, c.getString(c.getColumnIndex(str)));
            }
            list.add(map);
        }
        c.close();
        db.close();
        return list;
    }


    /**
     * 获取数据列表
     *
     * @param table  表名
     * @param fields 搜索的字段名数组
     * @param key    搜索条件对应的字段名
     * @param value  搜索条件对应的值
     */
    public static List<Map<String, String>> listData(String table, String[] fields, String key, String value) {
        List<Map<String, String>> list = new ArrayList<Map<String, String>>();
        String fstr = "";

        if (fields == null || fields.length == 0) {
            fstr = " * ";
            return null;
        } else {
            for (String str : fields) {
                fstr += " " + str + ",";
            }
            fstr = fstr.substring(0, fstr.length() - 1);
        }

        String wstr = "";
        if (StringUtil.isNotEmpty(key) && StringUtil.isNotEmpty(value)) {
            wstr = " WHERE " + key + " = '" + value + "'";
        }

        String sql = "SELECT " + fstr + " FROM " + table + " " + wstr;
        System.out.println("###sql:" + sql);

        Cursor c = db.rawQuery(sql, null);

        while (c.moveToNext()) {
            Map<String, String> map = new HashMap<String, String>();
            for (String str : fields) {
                map.put(str, c.getString(c.getColumnIndex(str)));
            }
            list.add(map);
        }
        c.close();
        db.close();
        return list;
    }

    /**
     * 获取数据
     *
     * @param table  表名
     * @param fields 搜索的字段名数组
     * @param key    搜索条件对应的字段名
     * @param value  搜索条件对应的值
     */
    public static Map<String, String> getData(String table, String[] fields, String key, String value) {
        Map<String, String> map = new HashMap<String, String>();
        String fstr = "";

        if (fields == null || fields.length == 0) {
            fstr = " * ";
            return null;
        } else {
            for (String str : fields) {
                fstr += " " + str + ",";
            }
            fstr = fstr.substring(0, fstr.length() - 1);
        }

        String wstr = "";
        if (StringUtil.isNotEmpty(key) && StringUtil.isNotEmpty(value)) {
            wstr = " WHERE " + key + " = '" + value + "'";
        }

        String sql = "SELECT " + fstr + " FROM " + table + " " + wstr;
        System.out.println("###sql:" + sql);

        Cursor c = db.rawQuery(sql, null);

        while (c.moveToNext()) {
            for (String str : fields) {
                map.put(str, c.getString(c.getColumnIndex(str)));
            }
        }
        c.close();
        db.close();
        return map;
    }


    /**
     * 获取一条数据
     *
     * @param table  表名
     * @param fields 搜索的字段名集合
     * @param key    搜索条件对应的字段名
     * @param value  搜索条件对应的值
     */
    public static Map<String, String> getData(String table, List<String> fields, String key, String value) {
        Map<String, String> map = new HashMap<String, String>();
        String fstr = "";

        if (fields == null || fields.size() == 0) {
            fstr = " * ";
            return null;
        } else {
            for (String str : fields) {
                fstr += " " + str + ",";
            }
            fstr = fstr.substring(0, fstr.length() - 1);
        }

        String wstr = "";
        if (StringUtil.isNotEmpty(key) && StringUtil.isNotEmpty(value)) {
            wstr = " WHERE " + key + " = '" + value + "'";
        }

        String sql = "SELECT " + fstr + " FROM " + table + " " + wstr;
        System.out.println("###sql:" + sql);

        Cursor c = db.rawQuery(sql, null);

        while (c.moveToNext()) {
            for (String str : fields) {
                map.put(str, c.getString(c.getColumnIndex(str)));
            }
        }
        c.close();
        db.close();
        return map;
    }

    /**
     * 保存地块数据
     *
     * @param datas
     * @param table
     */
    public static boolean add(List<Map<String, String>> datas, String table) {
        db.beginTransaction();
        String[] keys = getColumnNames(table);
        try {
            for (Map<String, String> map : datas) {
                ContentValues cv = new ContentValues();
                for (String key : keys) {
                    if (map.containsKey(key)) {
                        cv.put(key, map.get(key));
                    }
                }
                db.insert(table, "", cv);
            }

            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
            db.close();
        }
        return true;
    }

    /**
     * 统计数量
     *
     * @param table 表名
     */
    public static int count(String table) {
        int count = 0;

        String sql = "SELECT COUNT(*) AS num FROM " + table;
        System.out.println("###sql:" + sql);

        Cursor c = db.rawQuery(sql, null);

        while (c.moveToNext()) {
            count = c.getInt(c.getColumnIndex("num"));
        }
        c.close();
        db.close();

        return count;
    }

    /**
     * 统计数量
     *
     * @param table 表名
     * @param where 条件
     */
    public static int count(String table, String where) {
        int count = 0;

        String sql = "SELECT COUNT(*) AS num FROM " + table;
        if (!StringUtil.isEmpty(where)) {
            sql += " where " + where;
        }
        System.out.println("###sql:" + sql);

        Cursor c = db.rawQuery(sql, null);

        while (c.moveToNext()) {
            count = c.getInt(c.getColumnIndex("num"));
        }
        c.close();
        db.close();

        return count;
    }


    /**
     * 获取表的所有列
     */
    public static String[] getColumnNames(String table) {
        String[] cols = null;

        String sql = "PRAGMA table_info(" + table + ")";
        Cursor c = db.rawQuery(sql, null);

        if (c != null) {
            int columnIndex = c.getColumnIndex("name");
            if (-1 == columnIndex) {
                return null;
            }

            int i = 0;
            cols = new String[c.getCount()];
            while (c.moveToNext()) {
                cols[i] = c.getString(columnIndex);
                i++;
            }
            c.close();
        }

        return cols;
    }

}
